package com.example.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.easyexcel.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import java.io.*;
import java.net.URL;
import java.util.*;

/**
 * user表生成excel示例
 *
 * @author tu228
 */
@Slf4j
@RestController
@RequestMapping("/user")
public class UserExcelController {

    /**
     * 简单方式写01
     */
    @RequestMapping(value = "/simpleWrite01", method = RequestMethod.GET)
    public void simpleWrite01() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user1.xlsx";
        // 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
        EasyExcel.write(filename, User.class)
                .sheet("用户信息")
                .doWrite(getUserData());
    }

    /**
     * 简单方式写02
     */
    @RequestMapping(value = "/simpleWrite02", method = RequestMethod.GET)
    public void simpleWrite02() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user2.xlsx";
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
        // 创建Sheet对象
        WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
        // 向Excel中写入数据
        excelWriter.write(getUserData(), writeSheet);
        // 关闭流
        excelWriter.finish();
    }

    /**
     * 简单方式写03
     *    排除模型中的属性字段
     */
    @RequestMapping(value = "/simpleWrite03", method = RequestMethod.GET)
    public void simpleWrite03() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user3.xlsx";
        // 设置排除的属性 也可以在数据模型的字段上加@ExcelIgnore注解排除
        Set<String> excludeField = new HashSet<>();
        excludeField.add("hireDate");
        excludeField.add("userName");
        // 写Excel
        EasyExcel.write(filename, User.class)
                .excludeColumnFieldNames(excludeField)
                .sheet("用户信息")
                .doWrite(getUserData());
    }

    /**
     * 简单方式写04
     *    向表格中导出指定属性
     */
    @RequestMapping(value = "/simpleWrite04", method = RequestMethod.GET)
    public void simpleWrite04() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user4.xlsx";
        // 设置排除的属性 也可以在数据模型的字段上加@ExcelIgnore注解排除
        Set<String> includeFields = new HashSet<>();
        includeFields.add("userId");
        includeFields.add("userName");
        includeFields.add("salary");
        // 写Excel
        EasyExcel.write(filename, User.class)
                .includeColumnFieldNames(includeFields)
                .sheet("用户信息")
                .doWrite(getUserData());
    }

    /**
     * 简单方式写05
     */
    @RequestMapping(value = "/simpleWrite05", method = RequestMethod.GET)
    public void simpleWrite05() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user5.xlsx";
        // 向Excel中写入数据
        EasyExcel.write(filename, User.class)
                .sheet("用户信息")
                .doWrite(getUserData());
    }

    /**
     * 简单方式写06
     *    @ExcelProperty注解的value属性是一个数组类型, 设置多个head时会自动合并
     */
    @RequestMapping(value = "/simpleWrite06", method = RequestMethod.GET)
    public void simpleWrite06() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user6.xlsx";
        // 向Excel中写入数据
        EasyExcel.write(filename, ComplexHeadUser.class)
                .sheet("用户信息")
                .doWrite(getComplexHeadData());
    }

    /**
     * 简单方式写07
     *    重复写到Excel的同一个Sheet中
     */
    @RequestMapping(value = "/simpleWrite07", method = RequestMethod.GET)
    public void simpleWrite07() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user7.xlsx";
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
        // 创建Sheet对象
        WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
        // 向Excel的同一个Sheet重复写入数据
        for (int i = 0; i < 2; i++) {
            excelWriter.write(getUserData(), writeSheet);
        }
        // 关闭流
        excelWriter.finish();
    }

    /**
     * 简单方式写08
     *     写到Excel的不同Sheet中
     */
    @RequestMapping(value = "/simpleWrite08", method = RequestMethod.GET)
    public void simpleWrite08() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user8.xlsx";
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
        // 向Excel的同一个Sheet重复写入数据
        for (int i = 0; i < 2; i++) {
            // 创建Sheet对象
            WriteSheet writeSheet = EasyExcel.writerSheet("用户信息" + i).build();
            excelWriter.write(getUserData(), writeSheet);
        }
        // 关闭流
        excelWriter.finish();
    }

    /**
     * 简单方式写09
     *    日期和数字展示的样式进行格式化
     */
    @RequestMapping(value = "/simpleWrite09", method = RequestMethod.GET)
    public void simpleWrite09() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user9.xlsx";
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(filename, User09.class).build();
        // 向Excel的同一个Sheet重复写入数据
        for (int i = 0; i < 2; i++) {
            // 创建Sheet对象
            WriteSheet writeSheet = EasyExcel.writerSheet("用户信息" + i).build();
            excelWriter.write(getUserData(), writeSheet);
        }
        // 关闭流
        excelWriter.finish();
    }

    /**
     * 简单方式写10
     *    图片导入实体
     */
    @RequestMapping(value = "/simpleWrite10", method = RequestMethod.GET)
    public void simpleWrite10() throws IOException {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user10.xlsx";
        // 图片位置
        String imagePath = "E:\\office\\study\\tmp\\easyExcel\\img\\testImg01.jpg";
        // 网络图片
        URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");
        // 将图片读取到二进制数据中
        byte[] bytes = new byte[(int) new File(imagePath).length()];
        InputStream inputStream = new FileInputStream(imagePath);
        inputStream.read(bytes, 0, bytes.length);
        List<ImageData> imageDataList = new ArrayList<>();

        // 创建数据模板
        ImageData imageData = ImageData.builder()
                .file(new File(imagePath))
                .inputStream(new FileInputStream(imagePath))
                .str(imagePath)
                .byteArr(bytes)
                .url(url)
                .build();
        // 添加要写入的图片模型
        imageDataList.add(imageData);

        // 写数据
        EasyExcel.write(filename, ImageData.class)
                .sheet("帅哥")
                .doWrite(imageDataList);
    }

    /**
     * 简单方式写11
     *    设置写入Excel的列宽和行高
     */
    @RequestMapping(value = "/simpleWrite11", method = RequestMethod.GET)
    public void simpleWrite11() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user11.xlsx";
        // 构建数据
        List<WidthAndHeightData> dataList = new ArrayList<>();
        WidthAndHeightData data = WidthAndHeightData.builder()
                .string("字符串")
                .date(new Date())
                .doubleData(888.88)
                .build();
        dataList.add(data);
        // 向Excel中写入数据
        EasyExcel.write(filename, WidthAndHeightData.class)
                .sheet("行高和列宽测试")
                .doWrite(dataList);
    }

    /**
     * 简单方式写12
     *    通过注解形式设置写入Excel样式
     */
    @RequestMapping(value = "/simpleWrite12", method = RequestMethod.GET)
    public void testWrite12() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user12.xlsx";
        // 构建数据
        List<DemoStyleData> dataList = new ArrayList<>();
        DemoStyleData data = DemoStyleData.builder()
                .string("字符串")
                .date(new Date())
                .doubleData(888.88)
                .build();
        dataList.add(data);
        // 向Excel中写入数据
        EasyExcel.write(filename, DemoStyleData.class)
                .sheet("样式设置测试")
                .doWrite(dataList);

    }

    /**
     * 简单方式写13
     *    合并单元格
     */
    @RequestMapping(value = "/simpleWrite13", method = RequestMethod.GET)
    public void testWrite13() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user13.xlsx";
        // 构建数据
        List<DemoMergeData> dataList = new ArrayList<>();
        DemoMergeData data01 = DemoMergeData.builder().string("字符串01").date(new Date()).doubleData(111.11).build();
        DemoMergeData data02 = DemoMergeData.builder().string("字符串02").date(new Date()).doubleData(222.22).build();
        DemoMergeData data03 = DemoMergeData.builder().string("字符串03").date(new Date()).doubleData(333.33).build();
        DemoMergeData data04 = DemoMergeData.builder().string("字符串04").date(new Date()).doubleData(444.44).build();
        DemoMergeData data05 = DemoMergeData.builder().string("字符串05").date(new Date()).doubleData(555.55).build();
        DemoMergeData data06 = DemoMergeData.builder().string("字符串06").date(new Date()).doubleData(666.66).build();
        DemoMergeData data07 = DemoMergeData.builder().string("字符串07").date(new Date()).doubleData(777.77).build();
        DemoMergeData data08 = DemoMergeData.builder().string("字符串08").date(new Date()).doubleData(888.88).build();
        DemoMergeData data09 = DemoMergeData.builder().string("字符串09").date(new Date()).doubleData(999.99).build();
        DemoMergeData data10 = DemoMergeData.builder().string("字符串10").date(new Date()).doubleData(101010.10).build();
        dataList.add(data01);
        dataList.add(data02);
        dataList.add(data03);
        dataList.add(data04);
        dataList.add(data05);
        dataList.add(data06);
        dataList.add(data07);
        dataList.add(data08);
        dataList.add(data09);
        dataList.add(data10);
        // 向Excel中写入数据
        EasyExcel.write(filename, DemoMergeData.class)
                .sheet("单元格合并测试")
                .doWrite(dataList);
    }

    /**
     * 简单方式写14
     *    写的数据转换器
     */
    @RequestMapping(value = "/simpleWrite14", method = RequestMethod.GET)
    public void simpleWrite14() {
        String filename = "E:\\office\\study\\tmp\\easyExcel\\user14.xlsx";
        // 向Excel中写入数据
        EasyExcel.write(filename, UserModel.class)
                .sheet("用户信息")
                .doWrite(getUserData02());
    }


    /**
     * 根据user模板构建数据
     */
    private List<UserModel> getUserData02() {
        List<UserModel> users = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            UserModel user = UserModel.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? 0 : 2) // 性别枚举
                    .salary(i * 1000 + 1.111*i)
                    .hireDate(new Date())
                    .build();
            users.add(user);
        }
        return users;
    }


    /**
     * 根据user模板构建数据
     */
    private List<User> getUserData() {
        List<User> users = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            User user = User.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            users.add(user);
        }
        return users;
    }

    /**
     * 根据user模板构建数据
     */
    private List<ComplexHeadUser> getComplexHeadData() {
        List<ComplexHeadUser> users = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            ComplexHeadUser user = ComplexHeadUser.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            users.add(user);
        }
        return users;
    }

}
